/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package fr.p10.miage.gestionabsence.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

/**
 *
 * @author delati
 */
public class AgendaBDD {

    public AgendaBDD() {
    }

    public static Agenda trouverParId(int id) throws SQLException, Exception {
        Agenda agenda = new Agenda();
        String sql = "SELECT * FROM AGENDA WHERE IDCR = ?";
        PreparedStatement pst = Connection.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pst.setInt(1, id);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (resultat.first() == true) {
                agenda.init(resultat);
            }
            resultat.close();
        }
        return agenda;
    }

    public static AgendaDay getlisteAgendaPersonalise(Etudiant etudiant, String jour) throws SQLException, Exception {
        ArrayList<AgendaAffichage> listeAgenda = new ArrayList<AgendaAffichage>();
        String sql = "select distinct IDCR,salle.nom as salle,ec.nom as ec,debut,fin "
                + " from promotion,agenda,jour,salle,ec "
                + " where agenda.idj=jour.idj"
                + " and agenda.ids=salle.ids"
                + " and agenda.idp=promotion.idp"
                + " and agenda.idec=ec.idec"
                + " and promotion.idp=?"
                + " and jour.datee=? ";

        PreparedStatement pst = Connection.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pst.setInt(1, etudiant.getPromotionId());
        pst.setString(2, jour);

        ResultSet resultat = pst.executeQuery();
        while (resultat.next()) {
            AgendaAffichage agenda = new AgendaAffichage();
            agenda.initEtudiant(resultat);
            listeAgenda.add(agenda);
        }
        resultat.close();
        return new AgendaDay(listeAgenda);
    }

    public static AgendaDay getlisteAgendaIntervenant(Intervenant intervenant, String jour) throws SQLException, Exception {
        ArrayList<AgendaAffichage> listeAgenda = new ArrayList<AgendaAffichage>();
        String sql = "select distinct IDCR,salle.nom as salle,ec.nom as ec,formation.grade as grade, formation.niveau as niveau, formation.filiere as filiere, formation.parcours as parcours, formation.typee as typee, debut,fin "
                + " from promotion,agenda,jour,salle,ec,formation "
                + " where agenda.idj=jour.idj"
                + " and agenda.ids=salle.ids"
                + " and agenda.idp=promotion.idp"
                + " and promotion.idf=formation.idf"
                + " and agenda.idec=ec.idec"
                + " and agenda.idi=?"
                + " and jour.datee=? ";

        PreparedStatement pst = Connection.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pst.setInt(1, intervenant.getIdI());
        pst.setString(2, jour);

        ResultSet resultat = pst.executeQuery();
        while (resultat.next()) {
            AgendaAffichage agenda = new AgendaAffichage();
            agenda.init(resultat);
            listeAgenda.add(agenda);
        }
        resultat.close();
        return new AgendaDay(listeAgenda);
    } 
    
    public static AgendaAffichage getAgendaIntervenantById(int id) throws SQLException, Exception {
        AgendaAffichage agenda = new AgendaAffichage();
        String sql = "select distinct IDCR,salle.nom as salle,ec.nom as ec,formation.grade as grade, formation.niveau as niveau, formation.filiere as filiere, formation.parcours as parcours, formation.typee as typee, debut,fin "
                + " from promotion,agenda,jour,salle,ec,formation "
                + " where agenda.idj=jour.idj"
                + " and agenda.ids=salle.ids"
                + " and agenda.idp=promotion.idp"
                + " and promotion.idf=formation.idf"
                + " and agenda.idec=ec.idec"
                + " and agenda.idcr=?";

        PreparedStatement pst = Connection.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pst.setInt(1, id);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (resultat.first() == true) {
                agenda.init(resultat);
            } else {
                throw new Exception("Agenda inconnu");
            }
            resultat.close();
        }
        return agenda;
    } 
  
}